package com.epoint.webapp.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import com.epoint.webapp.dao.BlogMessageDAO;
import com.epoint.webapp.entity.BlogMessage;
import com.epoint.webapp.entity.Member;
import com.epoint.webapp.util.DateConversion;

public class BlogMessageDAOImpl implements BlogMessageDAO{
	private DataSource dataSource;
	private Connection conn = null ;
	private ResultSet rs = null ;
	private PreparedStatement smt = null ;
	
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	public void insert(BlogMessage blogMessage) {
		// TODO Auto-generated method stub
		String sql = "INSERT INTO blog_message(memberAccount,blogID,blogMessageContent,blogMessageResponse,blogMessageCreateDate) "
				+ "VALUES(?,?,?,'',NOW())";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setString(1, blogMessage.getAccount());
			smt.setInt(2, blogMessage.getBid());
			smt.setString(3, blogMessage.getContent());
			smt.executeUpdate();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
	}

	public void updateResponse(BlogMessage blogMessage) {
		// TODO Auto-generated method stub
		String sql = "UPDATE blog_message SET blogMessageResponse=?, blogMessageResponseDate=NOW() "
				+ "WHERE blogMessageID = ?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setString(1, blogMessage.getResponse());
			smt.setInt(2, blogMessage.getId());
			smt.executeUpdate();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
	}

	public void delete(BlogMessage blogMessage) {
		// TODO Auto-generated method stub
		String sql = "DELETE FROM blog_message WHERE blogMessageID = ?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, blogMessage.getId());
			smt.executeUpdate();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
	}

	public BlogMessage get(BlogMessage blogMessage) {
		// TODO Auto-generated method stub

		DateConversion dateConversion = new DateConversion();
		String sql = "SELECT * FROM blog_message a JOIN member b ON a.memberAccount = b.memberAccount "
				+ "WHERE blogMessageID = ?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, blogMessage.getId());
			rs = smt.executeQuery();
			while(rs.next()){
				Member member = new Member();
				member.setAccount(rs.getString("memberAccount"));
				member.setName(rs.getString("memberName"));
				blogMessage.setAccount(member.getAccount());
				blogMessage.setBid(rs.getInt("blogID"));
				blogMessage.setContent(rs.getString("blogMessageContent"));
				blogMessage.setResponse(rs.getString("blogMessageResponse"));
				blogMessage.setcDate(rs.getTimestamp("blogMessageCreateDate"));
				blogMessage.setcDateString(dateConversion.ConversionYMDHM(blogMessage.getcDate()));
				if(!blogMessage.getResponse().equals("")){
					blogMessage.setrDate(rs.getTimestamp("blogMessageResponseDate"));
					blogMessage.setrDateString(dateConversion.ConversionYMDHM(blogMessage.getrDate()));
				}
				blogMessage.setMember(member);
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return blogMessage;
	}

	public List<BlogMessage> getList(int blogID, int start, int size) {
		// TODO Auto-generated method stub
		DateConversion dateConversion = new DateConversion();
		List<BlogMessage> blogMessageList = new ArrayList<BlogMessage>();
		String sql = "SELECT * FROM blog_message a JOIN member b ON a.memberAccount = b.memberAccount "
				+ "WHERE blogID = ? ORDER BY blogMessageCreateDate DESC LIMIT ?,?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, blogID);
			smt.setInt(2, start);
			smt.setInt(3, size);
			rs = smt.executeQuery();
			while(rs.next()){
				BlogMessage blogMessage = new BlogMessage();
				Member member = new Member();
				member.setAccount(rs.getString("memberAccount"));
				member.setName(rs.getString("memberName"));
				blogMessage.setAccount(member.getAccount());
				blogMessage.setBid(blogID);
				blogMessage.setId(rs.getInt("blogMessageID"));
				blogMessage.setContent(rs.getString("blogMessageContent"));
				blogMessage.setResponse(rs.getString("blogMessageResponse"));
				blogMessage.setcDate(rs.getTimestamp("blogMessageCreateDate"));
				blogMessage.setcDateString(dateConversion.ConversionYMDHM(blogMessage.getcDate()));
				if(!blogMessage.getResponse().equals("")){
					blogMessage.setrDate(rs.getTimestamp("blogMessageResponseDate"));
					blogMessage.setrDateString(dateConversion.ConversionYMDHM(blogMessage.getrDate()));
				}
				blogMessage.setMember(member);
				blogMessageList.add(blogMessage);
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return blogMessageList;
	}

	public int countTotal(int blogID) {
		// TODO Auto-generated method stub
		int countTotal = 0;
		String sql = "SELECT COUNT(*) AS countTotal FROM blog_message WHERE blogID = ?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, blogID);
			rs = smt.executeQuery();
			if(rs.next()){
				countTotal = rs.getInt("countTotal");
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return countTotal;
	}


}
